package impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import java.util.ArrayList;
import java.util.List;



import dao.LogDao;
import model.Log;

import util.C3P0Util;

public class LogDaoImpl implements LogDao {

	@Override
	public boolean addLog(Log log) throws SQLException {
		Connection connection = null;
		PreparedStatement ps = null;
		int i = 0; // 判断sql语句是否成功执行
		System.out.println(log.toString());
		try {
			connection = C3P0Util.getConnection();
			// 不插入id 因为id为自增的
			String sql = "insert into t_log(id,personnel_id,time,sign,operation_id,operation_content) values(?,?,?,?,?,?)";
			ps = (PreparedStatement) connection.prepareStatement(sql);
			ps.setInt(1, log.getId());
			ps.setInt(2, log.getPersonnel_id());
			ps.setString(3, log.getTime());
			ps.setInt(4, log.getSign());
			ps.setInt(5, log.getOperation_id());
			ps.setString(6, log.getOperation_content());
			i = ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
			C3P0Util.close(connection);
		}
		if (i == 0) {
			return false;
		} else {
			return true;
		}
	}

	@Override
	public boolean deleteLog(Log log) throws SQLException {
		Connection connection = null;
		PreparedStatement ps = null;
		System.out.println(log.toString());
		int i = 0;
		try {
			connection = C3P0Util.getConnection();
			String sql = "delete from t_log where id =?";
			ps = (PreparedStatement) connection.prepareStatement(sql);
			ps.setInt(1, log.getId());
			i = ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
			C3P0Util.close(connection);
		}
		if (i == 0) {
			return false;
		} else {
			return true;
		}
	}

	@Override
	public boolean updateLog(Log log) throws SQLException {
		Connection connection = null;
		PreparedStatement ps = null;
		System.out.println(log.toString());
		int i = 0;
		try {
			connection = C3P0Util.getConnection();
			String sql = "update t_log set personnel_id=?,time=?,sign=?,operation_id=?,operation_content=? where id=?";
			ps = (PreparedStatement) connection.prepareStatement(sql);
			ps.setInt(1, log.getPersonnel_id());
			ps.setString(2, log.getTime());
			ps.setInt(3, log.getSign());
			ps.setInt(4, log.getOperation_id());
			ps.setString(5, log.getOperation_content());
			ps.setInt(6, log.getId());

			i = ps.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
			C3P0Util.close(connection);
		}
		if (i == 0) {
			return false;
		} else {
			return true;
		}
	}

	@Override
	public List<Log> queryLog(Log log) throws SQLException {
		// TODO Auto-generated method stub
		Connection connection = null;
		PreparedStatement ps = null;

		System.out.println(log.toString());

		// 返回值集合
		List<Log> list = new ArrayList<Log>();
		// 参数集合
		List<String> para = new ArrayList<String>();

		try {
			connection = C3P0Util.getConnection();
			String sql = "select * from t_log where 1=1";

			ps = (PreparedStatement) connection.prepareStatement(sql);

			if (log.getId() != 0) {
				sql += " and id = ?";
				Integer i_id = log.getId();
				para.add(i_id.toString());
			}
			if (log.getPersonnel_id() != 0) {
				sql += " and personnel_id = ?";
				Integer i_personnel_id = log.getPersonnel_id();
				para.add(i_personnel_id.toString());
			}
			if (!log.getTime().equals("2000-01-01")) {
				sql += " and time = ?";
				para.add(log.getTime());
			}	
			if (log.getSign() != 0) {
				sql += " and sign = ?";
				Integer i_sign = log.getSign();
				para.add(i_sign.toString());
			}
			if (log.getOperation_id() != 0) {
				sql += " and operation_id = ?";
				Integer i_operation_id = log.getOperation_id();
				para.add(i_operation_id.toString());
			}
			if (!log.getOperation_content().equals("")) {
				sql += " and operation_content like ?";
				para.add("%" + log.getOperation_content() + "%");
			}

			System.out.println(sql);
			ps = (PreparedStatement) connection.prepareStatement(sql);

			for (int i = 0; i < para.size(); i++) {
				ps.setString(i + 1, para.get(i));
			}

			ResultSet rs = ps.executeQuery();

			while (rs.next()) {
				Log l = new Log();
				l.setId(rs.getInt("id"));
				l.setPersonnel_id(rs.getInt("personnel_id"));
				l.setTime(rs.getString("time"));
				l.setSign(rs.getInt("sign"));
				l.setOperation_id(rs.getInt("operation_id"));
				l.setOperation_content(rs.getString("operation_content"));
				list.add(l);
				System.out.println(rs.getInt("id") + "  " + rs.getInt("personnel_id") + "  " + rs.getString("time")
						+ "  " + rs.getInt("sign") + "  " + rs.getInt("operation_id") + "  "
						+ rs.getString("operation_content"));
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
			C3P0Util.close(connection);
		}

		return list;
	}

	@Override
	public List<Log> queryLogByBeginAndEndTime(String begintime, String endtime) throws SQLException {
		Connection connection = null;
		PreparedStatement ps = null;
		// 返回值集合
		List<Log> list = new ArrayList<Log>();
	
		try {
			connection = C3P0Util.getConnection();
			String sql = "select * from t_log where "
					+ "time between cast(? as datetime) and  cast(? as datetime)";
			ps = (PreparedStatement) connection.prepareStatement(sql);
			ps.setString(1, begintime);
			ps.setString(2, endtime);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				Log l = new Log();
				l.setId(rs.getInt("id"));
				l.setPersonnel_id(rs.getInt("personnel_id"));
				l.setTime(rs.getString("time"));
				l.setSign(rs.getInt("sign"));
				l.setOperation_id(rs.getInt("operation_id"));
				l.setOperation_content(rs.getString("operation_content"));
				list.add(l);
				System.out.println(rs.getInt("id") + "  " + rs.getInt("personnel_id") + "  " + rs.getString("time")
						+ "  " + rs.getInt("sign") + "  " + rs.getInt("operation_id") + "  "
						+ rs.getString("operation_content"));
			}
		} catch (Exception e) {
			e.printStackTrace();
			throw e;
		} finally {
			C3P0Util.close(connection);
		}
		return list;
	}

}
